package com.dt.utils.excel;


import com.itextpdf.text.*;
import com.itextpdf.text.Font;
import com.itextpdf.text.pdf.*;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;

import javax.imageio.ImageIO;
import java.awt.image.BufferedImage;
import java.io.*;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;


/**
 * Excel转成pdf 并在转换过程中在指定位置添加图片
 * @author 啊涛
 * @version 1.0
 * @date 2021/9/10 9:12
 */
public class ExcelTransformPdfContainPicture {

	public static void main( String[] args ) throws Exception {
		//Excel 转 pdf ，过程中插入图片
		excelToPdf(new String( "C:\\Users\\ThinkPad\\Desktop\\ZS20190615185757\\周家变电站-10kV周水线306线路保护-WXH822C-201901.xls" ),
				"C:\\Users\\ThinkPad\\Desktop\\ZS20190615185757\\a.pdf");

		//pdf添加页码
		String orgPdfPath = "C:\\Users\\ThinkPad\\Desktop\\ZS20190615185757\\a.pdf" ;
		String outputPdfPath = "C:\\Users\\ThinkPad\\Desktop\\ZS20190615185757\\b.pdf" ;
		addPageNum(orgPdfPath, outputPdfPath);

	}
	public static byte[] excelToPdf(String excelPath, String pdfPath) throws Exception{
		InputStream in = new FileInputStream(excelPath);

		Workbook workbook;
		if (excelPath.endsWith(".xlsx")){
			workbook = new XSSFWorkbook(in);
		} else {
			workbook = new HSSFWorkbook(in);
		}

		/*//在Excel 指定位置插入图片
		java.io.ByteArrayOutputStream byteArrayOutputStream = new java.io.ByteArrayOutputStream( );
		BufferedImage  bufferedImage = ImageIO.read( new File( "C:\\Users\\ThinkPad\\Desktop\\fanshuiping.png" ) );
		ImageIO.write( bufferedImage , "png" , byteArrayOutputStream );
		PicturePosition picturePosition = new PicturePosition( 0 , 0 , 255 , 255 , ( short ) 5 , 37 , ( short ) 9, 38 );
		//AddPictureToExcel(workbook, byteArrayOutputStream, picturePosition);
		AddPictureToExcel03( workbook , byteArrayOutputStream , picturePosition );
		*/
		Sheet sheet = workbook.getSheetAt(0);
		java.io.ByteArrayOutputStream stream = new ByteArrayOutputStream();

		Document document = new Document( PageSize.A4);//此处根据excel大小设置pdf纸张大小
		PdfWriter writer = PdfWriter.getInstance(document, stream);
		document.setMargins(0, 0, 15, 15);//设置边距
		document.open();
		//获取sheet也中每一列的宽度
		float[] widths = getColWidth(sheet);
		PdfPTable table = new PdfPTable(widths);
		//		table.setWidthPercentage(90);
		int colCount = widths.length;
		BaseFont baseFont = BaseFont.createFont("C:\\Windows\\Fonts\\simsun.ttc,0", BaseFont.IDENTITY_H,
				BaseFont.EMBEDDED);//设置基本字体
		//		System.out.println( sheet.getFirstRowNum() );
		for (int r = sheet.getFirstRowNum(); r < sheet.getPhysicalNumberOfRows(); r++) {

			Row row = sheet.getRow(r);
			if (row != null) {
				//				System.out.println(row.getFirstCellNum() );
				if (row.getFirstCellNum()>0  ){
					PdfPCell pCell = new PdfPCell(new Phrase(""));
					pCell.setBorder(0);
					table.addCell(pCell);
				}

				for (int c =row.getFirstCellNum(); (c < row.getLastCellNum() && c < colCount) && c > -1; c++) {
					if (c >= row.getPhysicalNumberOfCells()) {
						PdfPCell pCell = new PdfPCell(new Phrase(""));
						pCell.setBorder(0);
						table.addCell(pCell);
						continue;
					}
					Cell excelCell = row.getCell(c);
					String value = "";
					if (excelCell != null) {
						value = excelCell.toString().trim();
						if (value != null && value.length() != 0){
							String dataFormat = excelCell.getCellStyle().getDataFormatString();//获取excel单元格数据显示样式
							if (dataFormat != "General" && dataFormat != "@"){
								try {
									String numStyle = getNumStyle(dataFormat);
									if (excelPath.endsWith(".xls")){
										value = getValue( ( HSSFCell )excelCell ,numStyle);
									}else {
										value = getValue( ( XSSFCell )excelCell ,numStyle);
									}
								} catch (Exception e) {

								}
							}
						}
					}
					org.apache.poi.ss.usermodel.Font excelFont = getExcelFont(workbook, excelCell, excelPath);

					//HSSFFont excelFont = excelCell.getCellStyle().getFont(workbook);
					Font pdFont = new Font(baseFont, excelFont.getFontHeightInPoints(),
							excelFont.getBoldweight() == 700 ? Font.BOLD : Font.NORMAL, BaseColor.BLACK);//设置单元格字体

					PdfPCell pCell = new PdfPCell(new Phrase(value, pdFont));

					boolean hasBorder = hasBorder(excelCell);
					if (!hasBorder){
						pCell.setBorder(0);
					}
					pCell.setHorizontalAlignment(getHorAglin(excelCell.getCellStyle().getAlignment()));
					pCell.setVerticalAlignment(getVerAglin(excelCell.getCellStyle().getVerticalAlignment()));

					pCell.setMinimumHeight(row.getHeightInPoints());
					int rows = 1;
					if (isMergedRegion(sheet, r, c)) {
						int[] span = getMergedSpan(sheet, r, c);
						rows = span[0];
						if (span[0] == 1 && span[1] == 1) {//忽略合并过的单元格
							continue;
						}
						pCell.setRowspan(span[0]);
						pCell.setColspan(span[1]);
						//给合并的空白行 添加 边框
						if (  span[0] > 1 && r> 0  ){
							pCell.setBorder( Rectangle.RIGHT|Rectangle.LEFT );
							pCell.setBorderColorLeft( BaseColor.BLACK );
							pCell.setBorderColorRight( BaseColor.BLACK );

						}

						r=r+span[0]-1;
						c = c + span[1] - 1;//合并过的列直接跳过
						if ( r==37 ){
							PdfPCell pCell222 = new PdfPCell(new Phrase(""));
							pCell222.setRowspan(span[0]-8);
							pCell222.setColspan(span[1]);
							pCell222.setBorder(0);
							pCell222.setMinimumHeight(13);
							pCell222.setBorder( Rectangle.RIGHT|Rectangle.LEFT );
							pCell222.setBorderColorLeft( BaseColor.BLACK );
							pCell222.setBorderColorRight( BaseColor.BLACK );
							table.addCell(pCell222);
							for ( int i = 1 ; i < span[0] -8; i++ ) {
								PdfPCell pCell555 = new PdfPCell(new Phrase(""));
								pCell555.setBorder(0);
								pCell555.setMinimumHeight(13);
								table.addCell(pCell555);
							}
							PdfPCell pCell666 = new PdfPCell(new Phrase(""));
							pCell666.setRowspan(8);
							pCell666.setColspan(1);
							pCell666.setBorder(0);
							pCell666.setMinimumHeight(13);
							table.addCell(pCell666);

							PdfPCell pCell333 = new PdfPCell(new Phrase(""));
							pCell333.setRowspan(8);
							pCell333.setColspan(span[1]-8);
							pCell333.setBorder(0);
							pCell333.setBorder( Rectangle.LEFT );
							pCell333.setBorderColorLeft( BaseColor.BLACK );
							pCell333.setMinimumHeight(13);
							table.addCell(pCell333);

							PdfPCell pCell444 = new PdfPCell(new Phrase(""));
							Image image = Image.getInstance("C:\\Users\\ThinkPad\\Desktop\\gongzhang.png");
							pCell444.setImage( image );
							pCell444.setRowspan(8);
							pCell444.setColspan(8);
							pCell444.setBorder(0);
							pCell444.setBorder( Rectangle.RIGHT );
							pCell444.setBorderColorRight( BaseColor.BLACK );
							pCell444.setMinimumHeight(13);
							table.addCell(pCell444);
							continue;
						}
					}
					if (r==38 && c ==8){
						Image image = Image.getInstance("C:\\Users\\ThinkPad\\Desktop\\fanshuiping.png");
						pCell.setImage( image );
					}
					if (r==38 && c ==16){
						Image image = Image.getInstance("C:\\Users\\ThinkPad\\Desktop\\fanshuiping.png");
						pCell.setImage( image );
					}
					if (r==38 && c ==24){
						Image image = Image.getInstance("C:\\Users\\ThinkPad\\Desktop\\fanshuiping.png");
						pCell.setImage( image );
					}
					table.addCell(pCell);
					//有Excel的问题，空白行合并之后，每行的最后一列不会添加到tab了，所以这里要在table中添加一个 空的 cell，合并的多少行，就添加多少行 - 1个 call
					for ( int i = 1 ; i < rows ; i++ ) {
						PdfPCell pCell222 = new PdfPCell(new Phrase(""));
						pCell222.setBorder(0);
						pCell222.setMinimumHeight(13);
						table.addCell(pCell222);
					}
				}
			} else {
				PdfPCell pCell = new PdfPCell(new Phrase(""));
				pCell.setBorder(0);
				pCell.setMinimumHeight(13);
				table.addCell(pCell);
			}
		}

		document.add(table);

		document.close();

		byte[] pdfByte = stream.toByteArray();
		stream.flush();
		stream.reset();
		stream.close();

		FileOutputStream outputStream = new FileOutputStream(pdfPath);
		outputStream.write(pdfByte);
		outputStream.flush();
		outputStream.close();

		return pdfByte;
	}
	//获取字体
	private static org.apache.poi.ss.usermodel.Font getExcelFont(Workbook workbook, Cell cell, String excelName){
		if (excelName.endsWith(".xls")){
			return (( HSSFCell )cell).getCellStyle().getFont(workbook);
		}
		return (( XSSFCell )cell).getCellStyle().getFont();
	}
	/**
	 * 判断excel单元格是否有边框
	 * @param excelCell
	 * @return
	 */
	private static boolean hasBorder(Cell excelCell) {
		short top = excelCell.getCellStyle().getBorderTop();
		short bottom = excelCell.getCellStyle().getBorderBottom();
		short left = excelCell.getCellStyle().getBorderLeft();
		short right = excelCell.getCellStyle().getBorderRight();
		return top + bottom + left + right > 2;
	}

	/**
	 * 获取excel单元格数据显示格式
	 * @param dataFormat
	 * @return
	 * @throws Exception
	 */
	private static String getNumStyle(String dataFormat) throws Exception {
		if (dataFormat == null || dataFormat.length() == 0){
			throw new Exception("");
		}
		if (dataFormat.indexOf("%") > -1){
			return dataFormat;
		} else{
			return dataFormat.substring(0, dataFormat.length()-2);
		}

	}
	/**
	 * 判断单元格是否是合并单元格
	 * @param sheet
	 * @param row
	 * @param column
	 * @return
	 */
	private static boolean isMergedRegion(Sheet sheet, int row, int column) {
		int sheetMergeCount = sheet.getNumMergedRegions();
		for (int i = 0; i < sheetMergeCount; i++) {
			CellRangeAddress range = sheet.getMergedRegion(i);
			int firstColumn = range.getFirstColumn();
			int lastColumn = range.getLastColumn();
			int firstRow = range.getFirstRow();
			int lastRow = range.getLastRow();
			if (row >= firstRow && row <= lastRow) {
				if (column >= firstColumn && column <= lastColumn) {
					return true;
				}
			}
		}
		return false;
	}

	/**
	 * 计算合并单元格合并的跨行跨列数
	 * @param sheet
	 * @param row
	 * @param column
	 * @return
	 */
	private static int[] getMergedSpan(Sheet sheet, int row, int column) {
		int sheetMergeCount = sheet.getNumMergedRegions();
		int[] span = { 1, 1 };
		for (int i = 0; i < sheetMergeCount; i++) {
			CellRangeAddress range = sheet.getMergedRegion(i);
			int firstColumn = range.getFirstColumn();
			int lastColumn = range.getLastColumn();
			int firstRow = range.getFirstRow();
			int lastRow = range.getLastRow();
			if (firstColumn == column && firstRow == row) {
				span[0] = lastRow - firstRow + 1;
				span[1] = lastColumn - firstColumn + 1;
				break;
			}
		}
		return span;
	}

	/**
	 * 获取excel中每列宽度的占比
	 * @param sheet
	 * @return
	 */
	private static float[] getColWidth(Sheet sheet) {
		int rowNum = getMaxColRowNum(sheet);
		Row row = sheet.getRow(rowNum);
		int cellCount = row.getPhysicalNumberOfCells();
		int[] colWidths = new int[cellCount];
		int sum = 0;

		for (int i = row.getFirstCellNum(); i < cellCount; i++) {
			Cell cell = row.getCell(i);
			if (cell != null) {
				colWidths[i] = sheet.getColumnWidth(i)+100;
				sum += sheet.getColumnWidth(i);
			}
		}

		float[] colWidthPer = new float[cellCount];
		for (int i = row.getFirstCellNum(); i < cellCount; i++) {
			colWidthPer[i] = (float) colWidths[i] / sum * 100;
		}
		return colWidthPer;
	}
	/**
	 * 获取excel中列数最多的行号
	 * @param sheet
	 * @return
	 */
	private static int getMaxColRowNum(Sheet sheet) {
		int rowNum = 0;
		int maxCol = 0;
		for (int r = sheet.getFirstRowNum(); r < sheet.getPhysicalNumberOfRows(); r++) {
			Row row = sheet.getRow(r);
			if (row != null && maxCol < row.getPhysicalNumberOfCells()) {
				maxCol = row.getPhysicalNumberOfCells();
				rowNum = r;
			}
		}
		return rowNum;
	}
	/**
	 * excel垂直对齐方式映射到pdf对齐方式
	 * @param aglin
	 * @return
	 */
	private static int getVerAglin(int aglin) {
		switch (aglin) {
			case 1:
				return com.itextpdf.text.Element.ALIGN_MIDDLE;
			case 2:
				return com.itextpdf.text.Element.ALIGN_BOTTOM;
			case 3:
				return com.itextpdf.text.Element.ALIGN_TOP;
			default:
				return com.itextpdf.text.Element.ALIGN_MIDDLE;
		}
	}
	/**
	 * excel水平对齐方式映射到pdf水平对齐方式
	 * @param aglin
	 * @return
	 */
	private static int getHorAglin(int aglin) {
		switch (aglin) {
			case 2:
				return com.itextpdf.text.Element.ALIGN_CENTER;
			case 3:
				return com.itextpdf.text.Element.ALIGN_RIGHT;
			case 1:
				return com.itextpdf.text.Element.ALIGN_LEFT;
			default:
				return com.itextpdf.text.Element.ALIGN_CENTER;
		}
	}


	//获取Excel中的的值
	private static String getValue(XSSFCell cell,String pattern) {
		if (cell.getCellType() == XSSFCell.CELL_TYPE_BOOLEAN) {
			return String.valueOf(cell.getBooleanCellValue());
		} else if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
			//20180622,支持日期格式    pattern.contains(  "reserved") 这个条件根据实际情况加
			if(HSSFDateUtil.isCellDateFormatted(cell)|| pattern.contains(  "reserved")){
				Date d = (Date) cell.getDateCellValue();
				DateFormat df2 = new SimpleDateFormat("yyyy年MM月dd日");//HH:mm:ss
				return df2.format(d);
			}
			//数字
			else{
				//使用DecimalFormat对double进行了格式化，随后使用format方法获得的String就是你想要的值了。
				DecimalFormat df = new DecimalFormat(pattern);
				return String.valueOf(df.format(cell.getNumericCellValue()));
			}

		} else {
			return String.valueOf(cell.getStringCellValue());
		}
	}
	//获取Excel中的的值
	private static String getValue(HSSFCell hssfCell,String pattern) {
		if (hssfCell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) {
			return String.valueOf(hssfCell.getBooleanCellValue());
		} else if (hssfCell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
			//20180622,支持日期格式     pattern.contains(  "reserved")  这个条件根据实际情况加
			if(HSSFDateUtil.isCellDateFormatted(hssfCell) || pattern.contains(  "reserved")){
				Date d = (Date) hssfCell.getDateCellValue();
				DateFormat df2 = new SimpleDateFormat("yyyy年MM月dd日");//HH:mm:ss
				return df2.format(d);
			}
			//数字
			else{
				//使用DecimalFormat对double进行了格式化，随后使用format方法获得的String就是你想要的值了。
				DecimalFormat df = new DecimalFormat(pattern);
				return String.valueOf(df.format(hssfCell.getNumericCellValue()));
			}
		} else {
			return String.valueOf(hssfCell.getStringCellValue());
		}
	}


	/**
	 * 给pdf 添加页码
	 * @param orgPdfPath
	 * @param outputPdfPath
	 * @return
	 */
	public static String addPageNum (String orgPdfPath, String outputPdfPath) {

		try (
				// 输出文件 流
				FileOutputStream fos = new FileOutputStream(outputPdfPath) ;){

			// 新建文档，默认A4大小
			Document document = new Document( PageSize.A4);
			PdfWriter writer = PdfWriter.getInstance(document, fos);
			// 设置页面监听事件，必须在open方法前
			writer.setPageEvent(new PageNumPdfPageEvent());
			document.open();
			// PDF内容体
			PdfContentByte pdfContent = writer.getDirectContent();
			// 读取 源PDF文件，进行一页一页复制，才能触发 添加页码的  页面监听事件
			PdfReader reader = new PdfReader(orgPdfPath);
			// 获取 源文件总页数
			int num = reader.getNumberOfPages();
			System.out.println("总页数：" + num);
			// 页面数是从1开始的
			for (int i = 1; i <= num; i++) {
				document.newPage();
				// 设置空页码进行展示
				writer.setPageEmpty(false);
				PdfImportedPage page = writer.getImportedPage(reader, i);
				// 复制好的页面，添加到内容去，触发事件监听
				pdfContent.addTemplate(page, 0, 0);
			}

			document.close();
			reader.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

		return outputPdfPath ;
	}

}
